# -*- coding: utf-8 -*-
"""
#
# Authors: limanman
# OsChina: http://my.oschina.net/pydevops/
# Purpose:
#
"""

import MySQLdb as mdb
from MySqlPool import Mysql
from datetime import datetime as dt_date_time
import time
import traceback

from logger import logger
import sys, getopt
import xlrd
import xlwt

reload(sys)
sys.setdefaultencoding('utf8')

COMPANY_COL = 0
PROJECT_COL = 1
GATEWAY_COL = 2
NETWORK_NAME_COL = 3
NETWORK_ID_COL = 4
SERIAL_NUMBER_COL = 5
PROTOCOL_TYPE_COL = 6
DEVICE_TYPE = 7
LAMP_TAB_COL = 8
DEVICE_ADDR_COL = 9
SECTION_COL = 10
LONGITUDE_COL = 11
LATITUDE_COL = 12
POLE_HEIGHT_COL = 13
POLE_DIAMETER_COL = 14
POLE_MATERIAL_COL = 15
LANTERN_TYPE_COL = 16
LAMP_TYPE_COL = 17
SOLAR_PANEL_TYPE_COL = 18
BATTERY_TYPE_COL = 19
SOLAR_PANEL_POWER_COL = 20
BATTERY_AH_COL = 21
LOAD_POWER_COL = 22


class RegisterFileDeCode(object):
    def __init__(self, inputFileName, outputFileName):
        self.mysql = Mysql()
        self.outputFileName = outputFileName
        self.readexcel = xlrd.open_workbook(inputFileName)
        #self.table = self.readexcel.sheet_by_index(0)  # 通过索引顺序获取
        self.table = self.readexcel.sheet_by_name(u'Sheet1')
        self.max_nrows = self.table.nrows
        self.network_info_list = []
        self.writeexcel = xlwt.Workbook(encoding='utf-8')
        self.wrtesheet = self.writeexcel.add_sheet('register_response')

    def save_output_file(self):
        self.writeexcel.save(self.outputFileName)

    def get_protocol_type_index(self, protype):
        if protype == 'TCP':
            return 0
        elif protype == 'NBIot':
            return 1
        elif protype == 'UDP':
            return 2
        elif protype == 'Lorawan':
            return 3
        else:
            return 0

    def create_new_project(self, addr):

        tblName = 'lampinfo'
        sql_head = "insert into " + tblName + "(projectid, networkid, status, address, createtime) values "
        values = ''

        values += "(" + "0, 0, 0,'" + str(addr) + "','" + dt_date_time.now().strftime('%Y-%m-%d %H:%M:%S') + "'),"
        if values.endswith(","):
            values = values[0:-1] + ";"
        if values != '':
            try:
                sql = sql_head + values
                print sql
                self.mysql.insertOne(sql)
            except:
                print(traceback.format_exc())
                return False
        return True

    def get_company_id(self, name):

        sql = "select id from company where name = '%s' limit 1" % name

        company_info_list = self.mysql.getAll(sql)
        company_info = company_info_list[0]
        if len(company_info) > 0:
            return True, company_info['id']
        return False, 0

    def search_project_id_byname(self, name, company_id):

        sql = "select id, area, networkcount, lampcount from project where projectname = '%s' and company = %d limit 1" \
                   % (name, company_id)

        project_info_list = self.mysql.getAll(sql)

        if len(project_info_list) > 0:
            project_info = project_info_list[0]
            return True, project_info
        return False, 0, []

    def get_network_id(self, networkname, project_id):

        sql = "select id, lampcount from network where networkname = '%s' and projectid = %d limit 1" \
                   % (networkname, project_id)

        network_info_list = self.mysql.getAll(sql)

        if len(network_info_list) > 0:
            network_info = network_info_list[0]
            return True, network_info['id'], network_info['lampcount']
        return False, 0, 0

    def remove_network_id(self, networkid, projectid, networkcount):

        sql = "delete from network where id = %s" % networkid

        self.mysql.delete(sql)
        self.update_project_network_count(projectid, False)
        return

    def create_new_network(self, row_data, projectinfo, lineindex):

        protype = self.get_protocol_type_index(row_data[PROTOCOL_TYPE_COL])

        netcount = projectinfo['networkcount'] + lineindex

        devicesn = str(row_data[SERIAL_NUMBER_COL])

        networkname = str(row_data[NETWORK_NAME_COL])
        if row_data[GATEWAY_COL] == 'direct':

            devicesn = str(int(row_data[DEVICE_ADDR_COL], 16))
            networkname = devicesn

        result, networkid = self.insert_zone_data(networkname, projectinfo['id'])
        if result == False:
            return

        tblName = 'network'
        sql_head = "insert into " + tblName + "(id, projectid, networkid, networkname, protocoltype," + \
                   "devicesn, status, area, section, gatewaytype, createtime) values "
        values = ''

        values += "(" + str(networkid) + "," + str(projectinfo['id']) + "," + str(netcount) + ",'" + \
                  str(networkname) + "'," + str(protype) + ",'" + \
                  str(devicesn) + "',0,'" + str(projectinfo['area']) + "','" + str(row_data[SECTION_COL]) + "','" + \
                  str(row_data[GATEWAY_COL]) + "','" + dt_date_time.now().strftime('%Y-%m-%d %H:%M:%S') + "'),"
        if values.endswith(","):
            values = values[0:-1] + ";"
        if values != '':
            try:
                sql = sql_head + values
                print sql
                self.mysql.insertOne(sql)
            except:
                print(traceback.format_exc())
                return False

        self.update_project_network_count(projectinfo['id'], True)
        return True

    def insert_zone_data(self, networkname, projectid):

        try:

            zone_info_sql = "insert into zone (name, parent, level) values ('%s', %s, 4);" % (networkname, projectid)

            ret = self.mysql.insertOne(zone_info_sql)


            sql = "select id from zone where name = '%s' and parent = %d and level = 4" % (networkname, projectid)
            zone_list = self.mysql.getAll(sql)
            zoneinfo = zone_list[0] if len(zone_list) > 0 else {}
            if len(zoneinfo) > 0:
                return True, zoneinfo['id']
            return False, 0

        except:
            print(traceback.format_exc())
            return False

    def remove_exist_lamp(self, projectid, networkid, lammpid):

        sql = "delete from lampinfo where id = %s" % lammpid

        self.mysql.delete(sql)
        self.update_project_device_count(projectid, False)
        self.update_network_device_count(networkid, False)
        return True


    def check_lamp_exist(self, address, rmflag):

        sql = "select id , projectid, networkid from lampinfo where address = '%s'" % address

        lampinfo_list = self.mysql.getAll(sql)
        if len(lampinfo_list) > 0:
            if rmflag:
                lampid = lampinfo_list[0]['id']
                projectid = lampinfo_list[0]['projectid']
                networkid = lampinfo_list[0]['networkid']
                self.remove_exist_lamp(projectid, networkid, lampid)
            else:
                return True
        return False

    def register_new_lamp(self, lampcount, plampcount, line_index, projectid, networkid, row_data):
        lampnumber = lampcount + line_index

        if len(row_data[LAMP_TAB_COL]) > 0:
            lampnumber = int(row_data[LAMP_TAB_COL])

        tblName = 'lampinfo'
        sql_head = "insert into " + tblName + "(number, projectid, networkid, address, section, longitude, latitude," + \
                   "poleheight, polediameter, polematerial, lighttype, lamptype, boardtype," + \
                   "batterytype, boardpower, batteryah, loadpower, createtime) values "
        values = ''

        values += "(" + str(lampnumber) + "," + str(projectid) + "," + str(networkid) + ",'" + \
                  str(row_data[DEVICE_ADDR_COL]) + "','" + \
                  str(row_data[SECTION_COL]) + "','" + str(row_data[LONGITUDE_COL]) + "','" +\
                  str(row_data[LATITUDE_COL]) + "','" + str(row_data[POLE_HEIGHT_COL]) + "','" +\
                  str(row_data[POLE_DIAMETER_COL]) + "','" + str(row_data[POLE_MATERIAL_COL]) + "','" + \
                  str(row_data[LANTERN_TYPE_COL]) + "','" + str(row_data[LAMP_TYPE_COL]) + "','" + \
                  str(row_data[SOLAR_PANEL_TYPE_COL]) + "','" + str(row_data[BATTERY_TYPE_COL]) + "','" + \
                  str(row_data[SOLAR_PANEL_POWER_COL]) + "','" + str(row_data[BATTERY_AH_COL]) + "','" + \
                  str(row_data[LOAD_POWER_COL]) + "','" + dt_date_time.now().strftime('%Y-%m-%d %H:%M:%S') + "'),"
        if values.endswith(","):
            values = values[0:-1] + ";"
        if values != '':
            try:
                sql = sql_head + values
                print sql
                self.mysql.insertOne(sql)
            except:
                print(traceback.format_exc())
                return False
        self.update_network_device_count(networkid, True)
        self.update_project_device_count(projectid, True)
        return True

    def update_project_device_count(self, projectid, flag):
        try:
            sql = "select lampcount from project where id = '%s'" % projectid
            project_list = self.mysql.getAll(sql)
            projectinfo = project_list[0] if len(project_list) > 0 else {}
            if len(projectinfo) > 0:
                lampcount = projectinfo['lampcount']
                if flag:
                    lampcount += 1
                else:
                    lampcount -= 1
                lamp_sql = "update project set lampcount = %s where id = %s" % (projectinfo['lampcount'], projectid)
                self.mysql.update(lamp_sql)
            return True
        except:
            print(traceback.format_exc())
            return False

    def update_project_network_count(self, projectid, flag):
        try:
            sql = "select networkcount from project where id = '%s'" % projectid
            project_list = self.mysql.getAll(sql)
            projectinfo = project_list[0] if len(project_list) > 0 else {}
            if len(projectinfo) > 0:
                networkcount = projectinfo['networkcount']
                if flag:
                    networkcount += 1
                else:
                    networkcount -= 1
                lamp_sql = "update project set networkcount = %s where id = %s" % (networkcount, projectid)
                self.mysql.update(lamp_sql)
                return True
        except:
            print(traceback.format_exc())
            return False

    def update_network_device_count(self, networkid, flag):
        try:
            sql = "select lampcount from network where id = '%s'" % networkid
            project_list = self.mysql.getAll(sql)
            projectinfo = project_list[0] if len(project_list) > 0 else {}
            if len(projectinfo) > 0:
                lampcount = projectinfo['lampcount']
                if flag:
                    lampcount += 1
                else:
                    lampcount -= 1
            lamp_sql = "update network set lampcount = %s where id = %s" % (lampcount, networkid)
            print(str(lamp_sql))
            self.mysql.update(lamp_sql)
            return True
        except:
            print(traceback.format_exc())
            return False

    def paseExcelLine(self, line_index):
        try:
            row_data = self.table.row_values(line_index)
            print(str(row_data))
            if row_data[GATEWAY_COL] == 'gateway':
                if len(row_data[COMPANY_COL]) <= 0 or len(row_data[PROJECT_COL].encode('utf-8')) <= 0:
                    self.wrtesheet.write(line_index, 0, row_data[DEVICE_ADDR_COL])
                    self.wrtesheet.write(line_index, 1, "company name or project name error!!!")
                    return False

                reflag, company_id = self.get_company_id(row_data[COMPANY_COL].encode('utf-8'))
                if reflag == False:
                    self.wrtesheet.write(line_index, 0, row_data[DEVICE_ADDR_COL])
                    self.wrtesheet.write(line_index, 1, "company not register!!!")
                    return False

                reflag, projectifno = self.search_project_id_byname(row_data[PROJECT_COL].encode('utf-8'), company_id)
                if reflag == False:
                    self.wrtesheet.write(line_index, 0, row_data[DEVICE_ADDR_COL])
                    self.wrtesheet.write(line_index, 1, "project not create!!!")
                    return False

                reflag, network_id, lampcount = self.get_network_id(row_data[NETWORK_NAME_COL].encode('utf-8'), projectifno['id'])
                if reflag == False:
                    self.create_new_network(row_data, projectifno, line_index)
                    refalg, network_id, lampcount = self.get_network_id(row_data[NETWORK_NAME_COL].encode('utf-8'), projectifno['id'])
                    if refalg == False:
                        self.wrtesheet.write(line_index, 0, row_data[DEVICE_ADDR_COL])
                        self.wrtesheet.write(line_index, 1, "create network error!!!")
                        return False

                self.wrtesheet.write(line_index, 0, row_data[DEVICE_ADDR_COL])
                self.wrtesheet.write(line_index, 1, "create network success!!!")
                return True

            elif row_data[GATEWAY_COL] == 'direct':
                if len(row_data[COMPANY_COL]) <= 0 or len(row_data[PROJECT_COL].encode('utf-8')) <= 0:
                    self.wrtesheet.write(line_index, 0, row_data[DEVICE_ADDR_COL])
                    self.wrtesheet.write(line_index, 1, "company name or project name error!!!")
                    print("company name or project name error!!!")
                    return False
                reflag, company_id = self.get_company_id(row_data[COMPANY_COL].encode('utf-8'))
                if reflag == False:
                    self.wrtesheet.write(line_index, 0, row_data[DEVICE_ADDR_COL])
                    self.wrtesheet.write(line_index, 1, "company not register!!!")
                    print("company not register!!!")
                    return False

                print("companyid %s" % (company_id))
                print(type(row_data[PROJECT_COL]))
                reflag, projectifno = self.search_project_id_byname(str(row_data[PROJECT_COL].encode('utf-8')), company_id)
                if reflag == False:
                    self.wrtesheet.write(line_index, 0, row_data[DEVICE_ADDR_COL])
                    self.wrtesheet.write(line_index, 1, "project not create!!!")
                    print("project not create!!!")
                    return False

                reflag, network_id, lampcount = self.get_network_id(str(int(row_data[DEVICE_ADDR_COL], 16)), projectifno['id'])
                if reflag:
                    self.remove_network_id(network_id, projectifno['id'], projectifno['networkcount'])
                self.create_new_network(row_data, projectifno, line_index)
                refalg, network_id, lampcount = self.get_network_id(str(int(row_data[DEVICE_ADDR_COL], 16)), projectifno['id'])
                if refalg == False:
                    self.wrtesheet.write(line_index, 0, row_data[DEVICE_ADDR_COL])
                    self.wrtesheet.write(line_index, 1, "create network error!!!")
                    print("create network error!!!")
                    return False

                reflag = self.check_lamp_exist(row_data[DEVICE_ADDR_COL], True)
                if reflag == True:
                    self.wrtesheet.write(line_index, 0, row_data[DEVICE_ADDR_COL])
                    self.wrtesheet.write(line_index, 1, "device had register !!!")
                    print("device had register !!!")
                    return False
                reflag = self.register_new_lamp(lampcount, projectifno['lampcount'], line_index, projectifno['id'], network_id, row_data)
                if reflag == True:
                    self.wrtesheet.write(line_index, 0, row_data[DEVICE_ADDR_COL])
                    self.wrtesheet.write(line_index, 1, "device register success!!!")
                    print("device register success!!! !!!")
                    return True
                self.wrtesheet.write(line_index, 0, row_data[DEVICE_ADDR_COL])
                self.wrtesheet.write(line_index, 1, "device register (mysql) error !!!")
                print("device register (mysql) error !!!")
            else:
                if row_data[PROTOCOL_TYPE_COL] == 'UDP' \
                        and row_data[GATEWAY_COL] == 'YES':
                    pass
                elif row_data[PROTOCOL_TYPE_COL] == 'UDP' \
                        and row_data[GATEWAY_COL] == 'NO':
                    pass
                elif row_data[PROTOCOL_TYPE_COL] == 'Lorawan' \
                        and row_data[GATEWAY_COL] == 'NO':
                    pass
        except:
            print(traceback.format_exc())
            return False
def main(argv):
    """Main function."""

    inputfile = ''
    outputfile = ''
    try:
        opts, args = getopt.getopt(argv, "hi:o:", ["ifile=", "ofile="])
    except getopt.GetoptError:
        print 'register_device.py -i <inputfile> -o <outputfile>'
        sys.exit(2)
    for opt, arg in opts:
        if opt == '-h':
            print 'test.py -i <inputfile> -o <outputfile>'
            sys.exit()
        elif opt in ("-i", "--ifile"):
            inputfile = arg
        elif opt in ("-o", "--ofile"):
            outputfile = arg
    print 'input file：', inputfile
    print 'output file：', outputfile
    if len(inputfile) <= 0 or len(outputfile) <= 0:
        print 'register_device.py -i <inputfile> -o <outputfile>'
        sys.exit(2)
    fileDecode = RegisterFileDeCode(inputfile, outputfile)
    for rx in range(1, fileDecode.max_nrows):
        fileDecode.paseExcelLine(rx)
    #fileDecode.update_zone_data()
    fileDecode.save_output_file()

if __name__ == '__main__':
    main(sys.argv[1:])
